# controllers/customer_controller.py
import csv
from io import StringIO
from datetime import datetime
from flask import render_template, request, redirect, url_for, make_response, jsonify
from sqlalchemy import func
from models.database import SessionLocal
from models.order import Order
from controllers.user_auth import requires_auth, requires_role
from services.order_service import get_country_name, format_phone_number, format_phone_number_without_prefix
from urllib.parse import unquote

class CustomerController:
    def __init__(self, app):
        self.app = app
        self.register_routes()
    
    def register_routes(self):
        """注册路由"""
        self.app.route('/customers')(self.customers)
        self.app.route('/customer/<customer_id>')(self.customer_detail)
        self.app.route('/export-customers')(self.export_customers_csv)
        self.app.route('/api/customer-orders/<customer_id>')(self.api_customer_orders)
        self.app.route('/api/customers')(self.api_customers)  # 添加新的API端点
    
    @requires_auth
    def customers(self):
        # 获取分页参数
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 20, type=int)
        
        db = SessionLocal()
        try:
            # 检查用户权限
            current_user = getattr(request, 'current_user', None)
            
            # 构建查询
            query = db.query(Order)
            
            # 根据用户角色应用过滤
            if current_user and current_user.role == "user" and current_user.country:
                query = query.filter(Order.customer_country == current_user.country)
            elif current_user and current_user.role == "manager":
                # manager可以查看所有国家的订单
                pass
            # root用户可以查看所有订单
            
            # 获取订单总数
            total_orders = query.count()
            
            # 获取已退款订单数量
            refunded_orders = query.filter(Order.status == 'refunded').count()
            
            # 获取唯一的来源站点列表
            source_query = db.query(Order.source).distinct().order_by(Order.source)
            if current_user and current_user.role == "user" and current_user.country:
                source_query = source_query.filter(Order.customer_country == current_user.country)
            unique_sources = source_query.all()
            unique_sources = [source[0] for source in unique_sources]
            
            # 获取唯一的国家列表
            country_query = db.query(Order.customer_country).distinct().order_by(Order.customer_country)
            if current_user and current_user.role == "user" and current_user.country:
                country_query = country_query.filter(Order.customer_country == current_user.country)
            unique_countries = country_query.all()
            unique_countries = [country[0] for country in unique_countries if country[0]]
            
            # 获取状态统计数据
            status_stats_query = db.query(Order.status, func.count(Order.id).label('count')).group_by(Order.status).order_by(func.count(Order.id).desc())
            if current_user and current_user.role == "user" and current_user.country:
                status_stats_query = status_stats_query.filter(Order.customer_country == current_user.country)
            status_stats = status_stats_query.all()
            
            # 获取搜索筛选参数
            customer_search = request.args.get('customer', '')
            country_search = request.args.get('country', '')
            city_search = request.args.get('city', '')
            phone_search = request.args.get('phone', '')
            
            # 应用搜索筛选条件
            if customer_search:
                query = query.filter(
                    (Order.customer_name.contains(customer_search)) |
                    (Order.customer_email.contains(customer_search)) |
                    (Order.customer_first_name.contains(customer_search)) |
                    (Order.customer_last_name.contains(customer_search))
                )
            if country_search:
                query = query.filter(Order.customer_country == country_search)
            if city_search:
                query = query.filter(Order.customer_city.contains(city_search))
            if phone_search:
                query = query.filter(Order.customer_phone.contains(phone_search))
            
            # 获取所有唯一客户（基于邮箱或姓名）
            # 首先获取所有唯一的邮箱
            email_customers_query = db.query(Order.customer_email).filter(
                Order.customer_email.isnot(None)
            ).distinct()
            email_customers = email_customers_query.all()
            
            # 然后获取所有唯一的名字（不与邮箱重复）
            name_customers_query = db.query(Order.customer_name).filter(
                Order.customer_email.is_(None), 
                Order.customer_name.isnot(None)
            ).distinct()
            name_customers = name_customers_query.all()
            
            # 计算总客户数
            total_customers = len(email_customers) + len(name_customers)
            
            # 重新获取所有客户数据用于分页显示
            # 首先按邮箱分组
            email_customers_query = query.filter(Order.customer_email.isnot(None)).distinct()
            email_customers = email_customers_query.all()
            
            # 然后获取没有邮箱但有姓名的客户
            name_customers_query = query.filter(
                Order.customer_email.is_(None), 
                Order.customer_name.isnot(None)
            ).distinct()
            name_customers = name_customers_query.all()
            
            # 合并所有客户
            all_customers = list(email_customers) + list(name_customers)
            
            # 初始化客户统计数据
            premium_customers = 0
            normal_customers = 0
            poor_customers = 0
            refunded_customers = 0  # 已退款订单的客户数
            
            # 计算所有客户的统计数据（不仅仅是当前页的客户）
            # 获取所有唯一客户用于统计计算
            all_customers_for_stats = list(email_customers) + list(name_customers)
            
            # 计算所有客户的质量统计
            for customer in all_customers_for_stats:
                # 获取该客户的所有订单
                if customer.customer_email:
                    orders_query = query.filter(Order.customer_email == customer.customer_email).order_by(Order.order_date.desc())
                else:
                    orders_query = query.filter(Order.customer_name == customer.customer_name).order_by(Order.order_date.desc())
                
                orders = orders_query.all()
                
                # 计算总订单数
                total_orders_customer = len(orders)
                
                # 检查是否有已退款订单
                has_refunded_orders = any(order.status == 'refunded' for order in orders)
                if has_refunded_orders:
                    refunded_customers += 1
                
                # 计算客户质量
                # 特殊规则：订单数小于3单且没有退款、取消、失败订单的客户直接归为一般客户
                refunded_orders = sum(1 for order in orders if order.status == 'refunded')
                cancelled_orders = sum(1 for order in orders if order.status == 'cancelled')
                failed_orders = sum(1 for order in orders if order.status == 'failed')
                
                if total_orders_customer < 3 and refunded_orders == 0 and cancelled_orders == 0 and failed_orders == 0:
                    customer_quality = 'normal'
                else:
                    # 基于成交金额占比评估客户质量
                    # 只计算已完成和处理中的订单金额作为有效订单金额
                    valid_orders_total = sum(order.total for order in orders if order.status in ['completed', 'processing'])
                    # 计算成功订单金额（已完成的订单）
                    successful_orders_total = sum(order.total for order in orders if order.status == 'completed')
                    if valid_orders_total > 0:
                        completion_rate = (successful_orders_total / valid_orders_total) * 100
                        if completion_rate >= 80:
                            customer_quality = 'premium'
                        elif completion_rate >= 50:
                            customer_quality = 'normal'
                        else:
                            customer_quality = 'poor'
                    else:
                        # 如果没有有效订单，根据是否有退款、取消、失败订单来判断
                        if refunded_orders > 0 or cancelled_orders > 0 or failed_orders > 0:
                            customer_quality = 'poor'
                        else:
                            customer_quality = 'normal'  # 默认为一般客户
                
                # 更新客户质量统计
                if customer_quality == 'premium':
                    premium_customers += 1
                elif customer_quality == 'normal':
                    normal_customers += 1
                elif customer_quality == 'poor':
                    poor_customers += 1
            
            # 计算分页信息
            total_pages = (total_customers + per_page - 1) // per_page
            page = max(1, min(page, total_pages))  # 确保页码在有效范围内
            offset = (page - 1) * per_page
            
            # 分页处理客户数据
            paginated_customers = all_customers[offset:offset + per_page]
            
            # 处理客户数据，聚合订单信息
            customer_list = []
            
            for customer in paginated_customers:
                # 获取该客户的所有订单
                if customer.customer_email:
                    orders_query = query.filter(Order.customer_email == customer.customer_email).order_by(Order.order_date.desc())
                else:
                    orders_query = query.filter(Order.customer_name == customer.customer_name).order_by(Order.order_date.desc())
                
                orders = orders_query.all()
                
                # 计算总订单数和总消费金额
                total_orders_customer = len(orders)
                # 总消费金额应该是已成交订单的总金额，而不是所有订单的总金额
                total_spent = sum(order.total for order in orders if order.status == 'completed')
                
                # 检查是否有已退款订单
                has_refunded_orders = any(order.status == 'refunded' for order in orders)
                if has_refunded_orders:
                    refunded_customers += 1
                
                # 获取最后购买时间
                last_order_date = max([order.order_date for order in orders if order.order_date], default=None)
                
                # 获取客户的常用货币（从最近的订单中获取）
                common_currency = "USD"  # 默认货币
                if orders:
                    # 使用最近订单的货币作为客户的常用货币
                    common_currency = orders[0].currency or "USD"
                
                # 计算客户质量
                # 特殊规则：订单数小于3单且没有退款、取消、失败订单的客户直接归为一般客户
                refunded_orders = sum(1 for order in orders if order.status == 'refunded')
                cancelled_orders = sum(1 for order in orders if order.status == 'cancelled')
                failed_orders = sum(1 for order in orders if order.status == 'failed')
                completion_rate = 0  # 初始化completion_rate变量
                if total_orders_customer < 3 and refunded_orders == 0 and cancelled_orders == 0 and failed_orders == 0:
                    customer_quality = 'normal'
                else:
                    # 基于成交金额占比评估客户质量
                    # 只计算已完成和处理中的订单金额作为有效订单金额
                    valid_orders_total = sum(order.total for order in orders if order.status in ['completed', 'processing'])
                    # 计算成功订单金额（已完成的订单）
                    successful_orders_total = sum(order.total for order in orders if order.status == 'completed')
                    if valid_orders_total > 0:
                        completion_rate = (successful_orders_total / valid_orders_total) * 100
                        if completion_rate >= 80:
                            customer_quality = 'premium'
                        elif completion_rate >= 50:
                            customer_quality = 'normal'
                        else:
                            customer_quality = 'poor'
                    else:
                        # 如果没有有效订单，根据是否有退款、取消、失败订单来判断
                        if refunded_orders > 0 or cancelled_orders > 0 or failed_orders > 0:
                            customer_quality = 'poor'
                        else:
                            customer_quality = 'normal'  # 默认为一般客户

                # 不再在这里更新客户质量统计，因为已经在前面计算过了
                
                customer_list.append({
                    'email': customer.customer_email,
                    'first_name': customer.customer_first_name,
                    'last_name': customer.customer_last_name,
                    'full_name': customer.customer_name,
                    'phone': customer.customer_phone,
                    'formatted_phone': format_phone_number(customer.customer_phone, customer.customer_country) if customer.customer_phone else '',
                    'address': customer.customer_address,
                    'address_2': customer.customer_address_2,
                    'city': customer.customer_city,
                    'state': customer.customer_state,
                    'postcode': customer.customer_postcode,
                    'country': customer.customer_country,
                    'total_orders': total_orders_customer,
                    'total_spent': total_spent,
                    'common_currency': common_currency,  # 添加常用货币信息
                    'last_order_date': last_order_date,
                    'customer_quality': customer_quality,
                    'completion_rate': round(completion_rate, 2) if total_spent > 0 else 0
                })
            
            # 创建分页对象
            class Pagination:
                def __init__(self, page, per_page, total):
                    self.page = page
                    self.per_page = per_page
                    self.total = total
                    self.pages = (total + per_page - 1) // per_page
                    self.has_prev = page > 1
                    self.has_next = page < self.pages
                    self.prev_num = page - 1 if self.has_prev else None
                    self.next_num = page + 1 if self.has_next else None
                
                def iter_pages(self, left_edge=2, left_current=2, right_current=3, right_edge=2):
                    """生成分页链接"""
                    last = 0
                    for num in range(1, self.pages + 1):
                        if num <= left_edge or \
                           (num > self.page - left_current - 1 and num < self.page + right_current) or \
                           num > self.pages - right_edge:
                            if last + 1 != num:
                                yield None
                            yield num
                            last = num

            pagination = Pagination(page, per_page, total_customers)

            return render_template('customers.html',
                                 customers=customer_list,
                                 unique_sources=unique_sources,
                                 unique_countries=unique_countries,  # 添加国家列表
                                 status_stats=status_stats,
                                 pagination=pagination,
                                 current_page=page,
                                 per_page=per_page,
                                 total_customers=total_customers,
                                 premium_customers=premium_customers,
                                 normal_customers=normal_customers,
                                 poor_customers=poor_customers,
                                 refunded_customers=refunded_customers,  # 添加已退款客户统计
                                 total_orders=total_orders,
                                 refunded_orders=refunded_orders)  # 添加已退款订单统计
        finally:
            db.close()
    
    @requires_role("manager")
    def add_customer(self):
        if request.method == 'POST':
            db = SessionLocal()
            try:
                data = request.form
                
                # 创建一个空订单来存储客户信息
                import datetime as dt
                order_date = dt.datetime.now(dt.UTC)
                
                # 获取所有表单数据
                customer_first_name = data.get('customer_first_name', '')
                customer_last_name = data.get('customer_last_name', '')
                customer_name = data.get('customer_name', '')
                # 如果没有提供完整姓名，则组合姓和名
                if not customer_name and (customer_first_name or customer_last_name):
                    customer_name = f"{customer_first_name} {customer_last_name}".strip()
                
                customer_email = data.get('customer_email', '')
                customer_phone = data.get('customer_phone', '')
                customer_address = data.get('customer_address', '')
                customer_address_2 = data.get('customer_address_2', '')
                customer_city = data.get('customer_city', '')
                customer_state = data.get('customer_state', '')
                customer_postcode = data.get('customer_postcode', '')
                customer_country = data.get('customer_country', '')
                
                # 格式化手机号为国际格式
                if customer_phone and customer_country:
                    customer_phone = format_phone_number(customer_phone, customer_country)
                
                # 创建一个占位订单来存储客户信息
                db.add(Order(
                    order_id=f"CUSTOMER-{datetime.now().strftime('%Y%m%d%H%M%S')}",
                    source="手动添加客户",
                    domain="",
                    customer_first_name=customer_first_name,
                    customer_last_name=customer_last_name,
                    customer_name=customer_name,
                    customer_email=customer_email,
                    customer_phone=customer_phone,
                    customer_address=customer_address,
                    customer_address_2=customer_address_2,
                    customer_city=customer_city,
                    customer_state=customer_state,
                    customer_postcode=customer_postcode,
                    customer_country=customer_country,
                    total=0,
                    currency="USD",
                    order_date=order_date,
                    status="completed",
                    item_count=0,
                    item_total=0,
                    shipping_total=0,
                    item_details="[]"
                ))
                db.commit()
                
                return redirect('/customers')
            except Exception as e:
                db.rollback()
                print(f"添加客户时出错: {e}")
            finally:
                db.close()
        return render_template('add_customer.html')
    
    @requires_auth
    def customer_detail(self, customer_id):
        db = SessionLocal()
        try:
            # URL解码客户ID
            customer_id = unquote(customer_id)
            
            # 检查用户权限
            current_user = getattr(request, 'current_user', None)
            
            # 构建查询
            query = db.query(Order)
            
            # 根据用户角色应用过滤
            if current_user and current_user.role == "user" and current_user.country:
                query = query.filter(Order.customer_country == current_user.country)
            elif current_user and current_user.role == "manager":
                # manager可以查看所有国家的订单
                pass
            # root用户可以查看所有订单
            
            # 根据邮箱或名称获取客户信息
            if '@' in customer_id:
                # 按邮箱查找
                customer_orders = query.filter(Order.customer_email == customer_id).order_by(Order.order_date.desc()).all()
            else:
                # 按姓名查找
                customer_orders = query.filter(Order.customer_name == customer_id).order_by(Order.order_date.desc()).all()
            
            if not customer_orders:
                return "客户未找到", 404
            
            # 检查用户是否有权限查看此客户（对于user角色）
            if current_user and current_user.role == "user" and current_user.country:
                customer_country = customer_orders[0].customer_country
                if customer_country != current_user.country:
                    return "权限不足", 403
            
            # 获取客户基本信息（从第一个订单中获取）
            first_order = customer_orders[0]
            customer_info = {
                'email': first_order.customer_email,
                'first_name': first_order.customer_first_name,
                'last_name': first_order.customer_last_name,
                'full_name': first_order.customer_name,
                'phone': first_order.customer_phone,
                'address': first_order.customer_address,
                'address_2': first_order.customer_address_2,
                'city': first_order.customer_city,
                'state': first_order.customer_state,
                'postcode': first_order.customer_postcode,
                'country': first_order.customer_country,
            }
            
            # 计算总订单数和总消费金额
            total_orders = len(customer_orders)
            # 总消费金额应该是已成交订单的总金额，而不是所有订单的总金额
            total_spent = sum(order.total for order in customer_orders if order.status == 'completed')
            
            # 获取客户的常用货币
            common_currency = "USD"  # 默认货币
            if customer_orders:
                common_currency = customer_orders[0].currency or "USD"
            
            return render_template('customer_detail.html', 
                                 customer=customer_info, 
                                 orders=customer_orders,
                                 total_orders=total_orders,
                                 total_spent=total_spent,
                                 common_currency=common_currency,  # 传递常用货币信息
                                 country_name_filter=get_country_name)
        finally:
            db.close()
    
    @requires_auth
    def export_customers_csv(self):
        db = SessionLocal()
        try:
            # 检查用户权限
            current_user = getattr(request, 'current_user', None)
            
            # 构建查询
            query = db.query(Order)
            
            # 根据用户角色应用过滤
            if current_user and current_user.role == "user" and current_user.country:
                query = query.filter(Order.customer_country == current_user.country)
            elif current_user and current_user.role == "manager":
                # manager可以查看所有国家的订单
                pass
            # root用户可以查看所有订单
            
            # 获取所有唯一客户（基于邮箱或姓名）
            # 首先获取所有唯一的邮箱
            email_customers_query = db.query(Order.customer_email).filter(
                Order.customer_email.isnot(None)
            ).distinct()
            email_customers = email_customers_query.all()
            
            # 然后获取所有唯一的名字（不与邮箱重复）
            name_customers_query = db.query(Order.customer_name).filter(
                Order.customer_email.is_(None), 
                Order.customer_name.isnot(None)
            ).distinct()
            name_customers = name_customers_query.all()
            
            # 计算总客户数
            total_customers = len(email_customers) + len(name_customers)
            
            # 重新获取所有客户数据用于导出
            # 首先按邮箱分组
            email_customers_query = query.filter(Order.customer_email.isnot(None)).distinct()
            email_customers = email_customers_query.all()
            
            # 然后获取没有邮箱但有姓名的客户
            name_customers_query = query.filter(
                Order.customer_email.is_(None), 
                Order.customer_name.isnot(None)
            ).distinct()
            name_customers = name_customers_query.all()
            
            # 合并所有客户
            all_customers = list(email_customers) + list(name_customers)
            
            # 处理客户数据，聚合订单信息
            customer_list = []
            for customer in all_customers:
                # 获取该客户的所有订单
                if customer.customer_email:
                    orders_query = query.filter(Order.customer_email == customer.customer_email).order_by(Order.order_date.desc())
                else:
                    orders_query = query.filter(Order.customer_name == customer.customer_name).order_by(Order.order_date.desc())
                
                orders = orders_query.all()
                
                # 计算总订单数和总消费金额
                total_orders = len(orders)
                # 总消费金额应该是已成交订单的总金额，而不是所有订单的总金额
                total_spent = sum(order.total for order in orders if order.status == 'completed')
                
                # 获取最后购买时间
                last_order_date = max([order.order_date for order in orders if order.order_date], default=None)
                
                # 获取客户的常用货币
                common_currency = "USD"  # 默认货币
                if orders:
                    common_currency = orders[0].currency or "USD"
                
                customer_list.append({
                    'email': customer.customer_email,
                    'first_name': customer.customer_first_name,
                    'last_name': customer.customer_last_name,
                    'full_name': customer.customer_name,
                    'phone': customer.customer_phone,
                    'formatted_phone': format_phone_number(customer.customer_phone, customer.customer_country) if customer.customer_phone else '',
                    'address': customer.customer_address,
                    'address_2': customer.customer_address_2,
                    'city': customer.customer_city,
                    'state': customer.customer_state,
                    'postcode': customer.customer_postcode,
                    'country': customer.customer_country,
                    'total_orders': total_orders,
                    'total_spent': total_spent,
                    'common_currency': common_currency,  # 添加常用货币信息
                    'last_order_date': last_order_date
                })
            
            # 创建CSV
            output = StringIO()
            writer = csv.writer(output)
            writer.writerow(['First Name', 'Last Name', 'Full Name', 'Email', 'Phone', 'Address', 'Address 2', 'City', 'State', 'Postcode', 'Country', 'Total Orders', 'Total Spent', 'Currency', 'Last Order Date'])
            
            for customer in customer_list:
                country_name = get_country_name(customer['country']) if customer['country'] else ''
                phone_number = customer['phone'] or ''
                # 如果有国家信息，格式化手机号
                if phone_number and customer['country']:
                    phone_number = format_phone_number(phone_number, customer['country'])
                writer.writerow([
                    customer['first_name'] or '',
                    customer['last_name'] or '',
                    customer['full_name'] or '',
                    customer['email'] or '',
                    phone_number,
                    customer['address'] or '',
                    customer['address_2'] or '',
                    customer['city'] or '',
                    customer['state'] or '',
                    customer['postcode'] or '',
                    country_name,
                    customer['total_orders'],
                    customer['total_spent'],
                    customer['common_currency'],  # 使用客户的常用货币
                    customer['last_order_date'].strftime('%Y-%m-%d %H:%M:%S') if customer['last_order_date'] else ''
                ])
            
            # 创建响应并设置正确的编码
            response = make_response(output.getvalue())
            response.headers["Content-Disposition"] = f"attachment; filename=customers_{datetime.now().strftime('%Y%m%d')}.csv"
            response.headers["Content-Type"] = "text/csv; charset=utf-8"
            return response
        finally:
            db.close()
    
    @requires_auth
    def api_customer_orders(self, customer_id):
        db = SessionLocal()
        try:
            # URL解码客户ID
            customer_id = unquote(customer_id)
            
            # 获取分页参数
            page = request.args.get('page', 1, type=int)
            per_page = request.args.get('per_page', 20, type=int)
            
            # 检查用户权限
            current_user = getattr(request, 'current_user', None)
            
            # 构建查询
            query = db.query(Order)
            
            # 根据用户角色应用过滤
            if current_user and current_user.role == "user" and current_user.country:
                query = query.filter(Order.customer_country == current_user.country)
            elif current_user and current_user.role == "manager":
                # manager可以查看所有国家的订单
                pass
            # root用户可以查看所有订单
            
            # 根据邮箱或名称获取客户订单
            if '@' in customer_id:
                # 按邮箱查找
                orders_query = query.filter(Order.customer_email == customer_id)
            else:
                # 按姓名查找
                orders_query = query.filter(Order.customer_name == customer_id)
            
            orders_query = orders_query.order_by(Order.order_date.desc())
            
            # 计算总数和分页
            total_orders = orders_query.count()
            total_pages = (total_orders + per_page - 1) // per_page
            page = max(1, min(page, total_pages))
            offset = (page - 1) * per_page
            
            # 分页查询
            orders = orders_query.offset(offset).limit(per_page).all()
            
            # 检查用户是否有权限查看此客户（对于user角色）
            if current_user and current_user.role == "user" and current_user.country and orders:
                customer_country = orders[0].customer_country
                if customer_country != current_user.country:
                    return "权限不足", 403
            
            # 转换为JSON格式
            orders_data = []
            for order in orders:
                # 提取运单物流数据
                tracking_data = []
                if order.item_details:
                    try:
                        import json
                        items = json.loads(order.item_details)
                        for item in items:
                            if 'meta_data' in item:
                                for meta in item['meta_data']:
                                    if meta.get('key') == '_vi_wot_order_item_tracking_data':
                                        tracking_info = json.loads(meta.get('value', '[]'))
                                        if isinstance(tracking_info, list):
                                            tracking_data.extend(tracking_info)
                                        elif isinstance(tracking_info, dict):
                                            tracking_data.append(tracking_info)
                    except Exception as e:
                        print(f"解析运单数据时出错: {e}")
                
                orders_data.append({
                    'id': order.id,
                    'order_id': order.order_id,
                    'source': order.source,
                    'domain': order.domain,
                    'total': order.total,
                    'currency': order.currency,
                    'order_date': order.order_date.isoformat() if order.order_date else None,
                    'status': order.status,
                    'tracking_data': tracking_data  # 添加物流信息
                })
            
            return jsonify({
                'orders': orders_data,
                'count': total_orders,
                'current_page': page,
                'total_pages': total_pages,
                'has_next': page < total_pages,
                'has_prev': page > 1
            })
        finally:
            db.close()
    
    @requires_auth
    def api_customers(self):
        # 获取分页参数
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 20, type=int)
        
        # 获取搜索筛选参数
        customer_search = request.args.get('customer', '')
        country_search = request.args.get('country', '')
        city_search = request.args.get('city', '')
        phone_search = request.args.get('phone', '')
        
        db = SessionLocal()
        try:
            # 检查用户权限
            current_user = getattr(request, 'current_user', None)
            
            # 构建查询
            query = db.query(Order)
            
            # 根据用户角色应用过滤
            if current_user and current_user.role == "user" and current_user.country:
                query = query.filter(Order.customer_country == current_user.country)
            elif current_user and current_user.role == "manager":
                # manager可以查看所有国家的订单
                pass
            # root用户可以查看所有订单
            
            # 应用搜索筛选条件
            if customer_search:
                query = query.filter(
                    (Order.customer_name.contains(customer_search)) |
                    (Order.customer_email.contains(customer_search)) |
                    (Order.customer_first_name.contains(customer_search)) |
                    (Order.customer_last_name.contains(customer_search))
                )
            if country_search:
                query = query.filter(Order.customer_country == country_search)
            if city_search:
                query = query.filter(Order.customer_city.contains(city_search))
            if phone_search:
                query = query.filter(Order.customer_phone.contains(phone_search))
            
            # 获取所有唯一客户（基于邮箱或姓名）
            # 首先获取所有唯一的邮箱
            email_customers_query = db.query(Order.customer_email).filter(
                Order.customer_email.isnot(None)
            ).distinct()
            email_customers = email_customers_query.all()
            
            # 然后获取所有唯一的名字（不与邮箱重复）
            name_customers_query = db.query(Order.customer_name).filter(
                Order.customer_email.is_(None), 
                Order.customer_name.isnot(None)
            ).distinct()
            name_customers = name_customers_query.all()
            
            # 计算总客户数
            total_customers = len(email_customers) + len(name_customers)
            
            # 重新获取所有客户数据用于分页显示
            # 首先按邮箱分组
            email_customers_query = query.filter(Order.customer_email.isnot(None)).distinct()
            email_customers = email_customers_query.all()
            
            # 然后获取没有邮箱但有姓名的客户
            name_customers_query = query.filter(
                Order.customer_email.is_(None), 
                Order.customer_name.isnot(None)
            ).distinct()
            name_customers = name_customers_query.all()
            
            # 合并所有客户
            all_customers = list(email_customers) + list(name_customers)
            
            # 初始化客户统计数据
            premium_customers = 0
            normal_customers = 0
            poor_customers = 0
            refunded_customers = 0  # 已退款订单的客户数
            
            # 计算所有客户的统计数据（不仅仅是当前页的客户）
            # 获取所有唯一客户用于统计计算
            all_customers_for_stats = list(email_customers) + list(name_customers)
            
            # 计算所有客户的质量统计
            for customer in all_customers_for_stats:
                # 获取该客户的所有订单
                if customer.customer_email:
                    orders_query = query.filter(Order.customer_email == customer.customer_email).order_by(Order.order_date.desc())
                else:
                    orders_query = query.filter(Order.customer_name == customer.customer_name).order_by(Order.order_date.desc())
                
                orders = orders_query.all()
                
                # 计算总订单数
                total_orders_customer = len(orders)
                
                # 检查是否有已退款订单
                has_refunded_orders = any(order.status == 'refunded' for order in orders)
                if has_refunded_orders:
                    refunded_customers += 1
                
                # 计算客户质量
                # 特殊规则：订单数小于3单且没有退款、取消、失败订单的客户直接归为一般客户
                refunded_orders = sum(1 for order in orders if order.status == 'refunded')
                cancelled_orders = sum(1 for order in orders if order.status == 'cancelled')
                failed_orders = sum(1 for order in orders if order.status == 'failed')
                
                if total_orders_customer < 3 and refunded_orders == 0 and cancelled_orders == 0 and failed_orders == 0:
                    customer_quality = 'normal'
                else:
                    # 基于成交金额占比评估客户质量
                    # 只计算已完成和处理中的订单金额作为有效订单金额
                    valid_orders_total = sum(order.total for order in orders if order.status in ['completed', 'processing'])
                    # 计算成功订单金额（已完成的订单）
                    successful_orders_total = sum(order.total for order in orders if order.status == 'completed')
                    if valid_orders_total > 0:
                        completion_rate = (successful_orders_total / valid_orders_total) * 100
                        if completion_rate >= 80:
                            customer_quality = 'premium'
                        elif completion_rate >= 50:
                            customer_quality = 'normal'
                        else:
                            customer_quality = 'poor'
                    else:
                        # 如果没有有效订单，根据是否有退款、取消、失败订单来判断
                        if refunded_orders > 0 or cancelled_orders > 0 or failed_orders > 0:
                            customer_quality = 'poor'
                        else:
                            customer_quality = 'normal'  # 默认为一般客户
                
                # 更新客户质量统计
                if customer_quality == 'premium':
                    premium_customers += 1
                elif customer_quality == 'normal':
                    normal_customers += 1
                elif customer_quality == 'poor':
                    poor_customers += 1
            
            # 计算分页信息
            total_pages = (total_customers + per_page - 1) // per_page
            page = max(1, min(page, total_pages))  # 确保页码在有效范围内
            offset = (page - 1) * per_page
            
            # 分页处理客户数据
            paginated_customers = all_customers[offset:offset + per_page]
            
            # 处理客户数据，聚合订单信息
            customer_list = []
            
            for customer in paginated_customers:
                # 获取该客户的所有订单
                if customer.customer_email:
                    orders_query = query.filter(Order.customer_email == customer.customer_email).order_by(Order.order_date.desc())
                else:
                    orders_query = query.filter(Order.customer_name == customer.customer_name).order_by(Order.order_date.desc())
                
                orders = orders_query.all()
                
                # 计算总订单数和总消费金额
                total_orders = len(orders)
                # 总消费金额应该是已成交订单的总金额，而不是所有订单的总金额
                total_spent = sum(order.total for order in orders if order.status == 'completed')
                
                # 检查是否有已退款订单
                has_refunded_orders = any(order.status == 'refunded' for order in orders)
                if has_refunded_orders:
                    refunded_customers += 1
                
                # 获取最后购买时间
                last_order_date = max([order.order_date for order in orders if order.order_date], default=None)
                
                # 获取客户的常用货币（从最近的订单中获取）
                common_currency = "USD"  # 默认货币
                if orders:
                    # 使用最近订单的货币作为客户的常用货币
                    common_currency = orders[0].currency or "USD"
                
                # 计算客户质量
                # 特殊规则：订单数小于3单且没有退款、取消、失败订单的客户直接归为一般客户
                refunded_orders = sum(1 for order in orders if order.status == 'refunded')
                cancelled_orders = sum(1 for order in orders if order.status == 'cancelled')
                failed_orders = sum(1 for order in orders if order.status == 'failed')
                completion_rate = 0  # 初始化completion_rate变量
                if total_orders < 3 and refunded_orders == 0 and cancelled_orders == 0 and failed_orders == 0:
                    customer_quality = 'normal'
                else:
                    # 基于成交金额占比评估客户质量
                    # 只计算已完成和处理中的订单金额作为有效订单金额
                    valid_orders_total = sum(order.total for order in orders if order.status in ['completed', 'processing'])
                    # 计算成功订单金额（已完成的订单）
                    successful_orders_total = sum(order.total for order in orders if order.status == 'completed')
                    if valid_orders_total > 0:
                        completion_rate = (successful_orders_total / valid_orders_total) * 100
                        if completion_rate >= 80:
                            customer_quality = 'premium'
                        elif completion_rate >= 50:
                            customer_quality = 'normal'
                        else:
                            customer_quality = 'poor'
                    else:
                        # 如果没有有效订单，根据是否有退款、取消、失败订单来判断
                        if refunded_orders > 0 or cancelled_orders > 0 or failed_orders > 0:
                            customer_quality = 'poor'
                        else:
                            customer_quality = 'normal'  # 默认为一般客户
                
                # 不再在这里更新客户质量统计，因为已经在前面计算过了
                
                customer_list.append({
                    'email': customer.customer_email,
                    'first_name': customer.customer_first_name,
                    'last_name': customer.customer_last_name,
                    'full_name': customer.customer_name,
                    'phone': customer.customer_phone,
                    'formatted_phone': format_phone_number(customer.customer_phone, customer.customer_country) if customer.customer_phone else '',
                    'address': customer.customer_address,
                    'address_2': customer.customer_address_2,
                    'city': customer.customer_city,
                    'state': customer.customer_state,
                    'postcode': customer.customer_postcode,
                    'country': customer.customer_country,
                    'total_orders': total_orders,
                    'total_spent': total_spent,
                    'common_currency': common_currency,  # 添加常用货币信息
                    'last_order_date': last_order_date,
                    'customer_quality': customer_quality,
                    'completion_rate': round(completion_rate, 2) if total_spent > 0 else 0
                })
            
            # 获取状态统计数据
            status_stats_query = db.query(Order.status, func.count(Order.id).label('count')).group_by(Order.status).order_by(func.count(Order.id).desc())
            if current_user and current_user.role == "user" and current_user.country:
                status_stats_query = status_stats_query.filter(Order.customer_country == current_user.country)
            status_stats = status_stats_query.all()
            
            # 获取订单总数和已退款订单数
            total_orders = query.count()
            refunded_orders = query.filter(Order.status == 'refunded').count()
            
            return jsonify({
                'customers': customer_list,
                'count': total_customers,
                'current_page': page,
                'total_pages': total_pages,
                'has_next': page < total_pages,
                'has_prev': page > 1,
                'status_counts': [{'status': stat[0], 'count': stat[1]} for stat in status_stats],
                'customer_stats': {
                    'premium': premium_customers,
                    'normal': normal_customers,
                    'poor': poor_customers
                },
                'order_stats': {
                    'total': total_orders,
                    'refunded': refunded_orders  # 添加已退款订单统计
                }
            })
        finally:
            db.close()